package gentle.service.impl;

import com.baomidou.mybatisplus.plugins.Page;
import gentle.base.StringUtil;
import gentle.entity.CustomerInfo;
import gentle.entity.SearchCustomerInfo;
import gentle.mapper.CardDetailDao;
import gentle.service.ICardDetailService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import gentle.base.SqlMapper;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@Transactional
public class CardDetailServiceImpl implements ICardDetailService {
    @Autowired
    private SqlMapper sqlMapper;

    @Autowired
    private CardDetailDao dao;

    public HashMap sumCardDetail(String reach,String customerStatus,String fieldTc,String modeName,String checkoutMethod,String startTime,String endTime,String outpatientName,String abbreviation){
        String sql1="";
        if (StringUtil.isNotEmpty(reach)) {
            sql1 += " and (t2.customer_name like CONCAT('%','" + reach + "','%') or t2.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " t2.wechat_number like CONCAT('%','" + reach + "','%') or t2.medical_record_number like CONCAT('%','" + reach + "','%') " +
                    " or t2.szm like CONCAT('%','" + reach + "','%'))";
        }
        if (StringUtil.isNotEmpty(customerStatus)) {
            sql1 += " and t2.customer_status = '" + customerStatus + "'";
        }
        String sql = "select sum(t1.actual_account_balance) as borrowAmount_sum " +
                " from reservemoney_deal t1 " +
                " where reservations_id in (select reservations_id from " + abbreviation + "customer_info t2 where 1=1"+sql1+" group by reservations_id) " +
                " and add_store='"+outpatientName+"'";
        if (StringUtil.isNotEmpty(startTime)) {
            sql += " and t1.recharge_time >= '" + startTime + "'";
        }
        if (StringUtil.isNotEmpty(endTime)) {
            sql += " and t1.recharge_time <= '" + endTime + "'";
        }
        if (StringUtil.isNotEmpty(fieldTc)) {
            sql += " and t1.tc_name = '" + fieldTc + "'";
        }
        if (StringUtil.isNotEmpty(modeName)) {
            sql += " and t1.mode = '" + modeName + "'";
        }
        if (StringUtil.isNotEmpty(checkoutMethod)) {
            String checkoutMethods[] = checkoutMethod.split(",");
            checkoutMethod="";
            if (checkoutMethods.length == 1) {
                checkoutMethod = checkoutMethods[0];
            } else {
                for (int i = 0; i < checkoutMethods.length; i++) {
                    if (i == (checkoutMethods.length - 1)) {
                        checkoutMethod += "'" + checkoutMethods[i];
                    } else if (i == 0) {
                        checkoutMethod += checkoutMethods[i] + "'" + ",";
                    } else {
                        checkoutMethod += "'" + checkoutMethods[i] + "'" + ",";
                    }
                }
            }
            sql += " and t1.checkout_method in ('" + checkoutMethod + "')";
        }
        Map<String, Object> result = sqlMapper.selectOne(sql.toString());
        HashMap map = new HashMap();
        if (result != null) {
            map.put("borrowAmount_sum", result.get("borrowAmount_sum"));
        } else {
            map.put("borrowAmount_sum", "0");
        }
        return map;
    }

    public List<CustomerInfo> selectPageListByQueryGetMap(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            params.put("endTime", searchInfo.getEndTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getModeName())) {
            params.put("modeName", searchInfo.getModeName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCheckoutMethod())) {
            String checkoutMethod = "";
            String checkoutMethods[] = searchInfo.getCheckoutMethod().split(",");
            if (checkoutMethods.length == 1) {
                checkoutMethod = checkoutMethods[0];
            } else {
                for (int i = 0; i < checkoutMethods.length; i++) {
                    if (i == (checkoutMethods.length - 1)) {
                        checkoutMethod += "'" + checkoutMethods[i];
                    } else if (i == 0) {
                        checkoutMethod += checkoutMethods[i] + "'" + ",";
                    } else {
                        checkoutMethod += "'" + checkoutMethods[i] + "'" + ",";
                    }
                }
            }
            params.put("checkoutMethod", " t1.checkout_method in ('" + checkoutMethod + "')");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        params.put("addStore", "'"+searchInfo.getOutpatientName()+"'");
        params.put("tableName", tableHead+"customer_info");
        params.put("table1", table1);
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryPage(page, params);
    }

    public int getQueryPagesCount(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            params.put("endTime", searchInfo.getEndTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getModeName())) {
            params.put("modeName", searchInfo.getModeName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCheckoutMethod())) {
            String checkoutMethod = "";
            String checkoutMethods[] = searchInfo.getCheckoutMethod().split(",");
            if (checkoutMethods.length == 1) {
                checkoutMethod = checkoutMethods[0];
            } else {
                for (int i = 0; i < checkoutMethods.length; i++) {
                    if (i == (checkoutMethods.length - 1)) {
                        checkoutMethod += "'" + checkoutMethods[i];
                    } else if (i == 0) {
                        checkoutMethod += checkoutMethods[i] + "'" + ",";
                    } else {
                        checkoutMethod += "'" + checkoutMethods[i] + "'" + ",";
                    }
                }
            }
            params.put("checkoutMethod", " t1.checkout_method in ('" + checkoutMethod + "')");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        params.put("addStore", "'"+searchInfo.getOutpatientName()+"'");
        params.put("tableName", tableHead+"customer_info");
        params.put("table1", table1);
        return dao.getQueryPagesCount(page, params);
    }

    public List<CustomerInfo> selectPageDetailListByQueryGetMap(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            params.put("endTime", searchInfo.getEndTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        params.put("addStore", "'"+searchInfo.getOutpatientName()+"'");
        params.put("tableName", tableHead+"customer_info");
        params.put("table1", table1);
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryDetailPage(page, params);
    }

    public int getQueryPagesDetailCount(Page<CustomerInfo> page, SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            params.put("endTime", searchInfo.getEndTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        params.put("addStore", "'"+searchInfo.getOutpatientName()+"'");
        params.put("tableName", tableHead+"customer_info");
        params.put("table1", table1);
        return dao.getQueryPagesDetailCount(page, params);
    }

    public void cardDetailExport(SearchCustomerInfo searchInfo, HttpServletResponse response) {
        try {
            List<CustomerInfo> list = this.getcardDetailExport(searchInfo);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
            for (int i = 0; i < 20; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("病历号");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("客户姓名");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("客户电话");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("客户状态");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("TC");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell = row.createCell(5);
            cell.setCellValue("交易类型");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("收费方式");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("金额");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("时间");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("储值备注");
            cell.setCellStyle(style);
            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                CustomerInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 20; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(map.getMedicalRecordNumber());
                row.getCell(1).setCellValue(map.getCustomerName());
                row.getCell(2).setCellValue(map.getCustomerPhone());
                row.getCell(3).setCellValue(map.getCustomerStatus());
                row.getCell(4).setCellValue(map.getFieldTc());
                row.getCell(5).setCellValue(map.getCheckoutMethod());
                row.getCell(6).setCellValue(map.getModeName());
                row.getCell(7).setCellValue(map.getActualAccountBalance());
                row.getCell(8).setCellValue(map.getRechargeTime());
                row.getCell(9).setCellValue(map.getRemarks());
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void cardDetailExportTwo(SearchCustomerInfo searchInfo, HttpServletResponse response) {
        try {
            List<CustomerInfo> list = this.cardDetailExportTwo(searchInfo);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
            for (int i = 0; i < 20; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("病历号");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("客户姓名");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("客户电话");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("客户状态");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("TC");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("储值交易类型");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("储值收费方式");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("储值消费");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("储值余额");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("时间");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("储值备注");
            cell.setCellStyle(style);
            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                CustomerInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 20; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(map.getMedicalRecordNumber());
                row.getCell(1).setCellValue(map.getCustomerName());
                row.getCell(2).setCellValue(map.getCustomerPhone());
                row.getCell(3).setCellValue(map.getCustomerStatus());
                row.getCell(4).setCellValue(map.getFieldTc());
                row.getCell(5).setCellValue(map.getCheckoutMethod());
                row.getCell(6).setCellValue(map.getModeName());
                row.getCell(7).setCellValue(map.getActualAccountBalance());
                row.getCell(8).setCellValue(map.getMoneyBalance()+map.getActualAccountBalance());
                row.getCell(9).setCellValue(map.getRechargeTime());
                row.getCell(10).setCellValue(map.getRemarks());
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public List<CustomerInfo> cardDetailExportTwo(SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        Map<String, Object> params = new HashMap<String, Object>();
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            params.put("endTime", searchInfo.getEndTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        params.put("addStore", "'"+searchInfo.getOutpatientName()+"'");
        params.put("tableName", tableHead+"customer_info");
        params.put("table1", table1);
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.cardDetailExportTwo(params);
    }

    public List<CustomerInfo> getcardDetailExport(SearchCustomerInfo searchInfo) {
        String tableHead = searchInfo.getAbbreviation();
        String table1 = tableHead + "deal_info";
        Map<String, Object> params = new HashMap<String, Object>();
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartTime())) {
            params.put("startTime", searchInfo.getStartTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndTime())) {
            params.put("endTime", searchInfo.getEndTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFieldTc())) {
            params.put("fieldTc", searchInfo.getFieldTc());
        }
        if (StringUtil.isNotEmpty(searchInfo.getModeName())) {
            params.put("modeName", searchInfo.getModeName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCheckoutMethod())) {
            String checkoutMethod = "";
            String checkoutMethods[] = searchInfo.getCheckoutMethod().split(",");
            if (checkoutMethods.length == 1) {
                checkoutMethod = checkoutMethods[0];
            } else {
                for (int i = 0; i < checkoutMethods.length; i++) {
                    if (i == (checkoutMethods.length - 1)) {
                        checkoutMethod += "'" + checkoutMethods[i];
                    } else if (i == 0) {
                        checkoutMethod += checkoutMethods[i] + "'" + ",";
                    } else {
                        checkoutMethod += "'" + checkoutMethods[i] + "'" + ",";
                    }
                }
            }
            params.put("checkoutMethod", " t1.checkout_method in ('" + checkoutMethod + "')");
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerStatus())) {
            params.put("customerStatus", searchInfo.getCustomerStatus());
        }
        params.put("addStore", "'"+searchInfo.getOutpatientName()+"'");
        params.put("tableName", tableHead+"customer_info");
        params.put("table1", table1);
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getcardDetailExport(params);
    }
}